/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.phoenix.parse;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.io.IOException;
import java.io.StringReader;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import org.apache.hadoop.hbase.util.Pair;
import org.apache.phoenix.exception.PhoenixParserException;
import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.jdbc.PhoenixStatement.Operation;
import org.apache.phoenix.schema.PTable;
import org.apache.phoenix.schema.SortOrder;
import org.junit.Test;

import org.apache.phoenix.thirdparty.com.google.common.base.Joiner;

public class QueryParserTest {
  private <T extends BindableStatement> T parseQuery(String sql, Class<T> type)
    throws IOException, SQLException {
    SQLParser parser = new SQLParser(new StringReader(sql));
    BindableStatement stmt = null;
    stmt = parser.parseStatement();
    if (stmt.getOperation() != Operation.QUERY) {
      return type != null ? type.cast(stmt) : null;
    }
    String newSQL = stmt.toString();
    SQLParser newParser = new SQLParser(new StringReader(newSQL));
    BindableStatement newStmt = null;
    try {
      newStmt = newParser.parseStatement();
    } catch (SQLException e) {
      fail("Unable to parse new:\n" + newSQL);
    }
    assertEquals("Expected equality:\n" + sql + "\n" + newSQL, stmt, newStmt);
    return type != null ? type.cast(stmt) : null;
  }

  private <T extends BindableStatement> T parseQuery(String sql) throws IOException, SQLException {
    return parseQuery(sql, null);
  }

  private void parseQueryThatShouldFail(String sql) throws Exception {
    try {
      parseQuery(sql);
      fail("Query should throw a PhoenixParserException \n " + sql);
    } catch (PhoenixParserException e) {
    }
  }

  private void parseQueryThatShouldFailWithSQLException(String sql) throws Exception {
    try {
      parseQuery(sql);
      fail("Query should throw a PhoenixParserException \n " + sql);
    } catch (SQLException e) {
    }
  }

  @Test
  public void testParseGrantQuery() throws Exception {

    String sql0 = "GRANT 'RX' ON SYSTEM.\"SEQUENCE\" TO 'user'";
    parseQuery(sql0);
    String sql1 = "GRANT 'RWXCA' ON TABLE some_table0 TO 'user0'";
    parseQuery(sql1);
    String sql2 = "GRANT 'RWX' ON some_table1 TO 'user1'";
    parseQuery(sql2);
    String sql3 = "GRANT 'CA' ON SCHEMA some_schema2 TO 'user2'";
    parseQuery(sql3);
    String sql4 = "GRANT 'RXW' ON some_table3 TO GROUP 'group3'";
    parseQuery(sql4);
    String sql5 = "GRANT 'RXW' ON \"some_schema5\".\"some_table5\" TO GROUP 'group5'";
    parseQuery(sql5);
    String sql6 = "GRANT 'RWA' TO 'user6'";
    parseQuery(sql6);
    String sql7 = "GRANT 'A' TO GROUP 'group7'";
    parseQuery(sql7);
    String sql8 = "GRANT 'ARXRRRRR' TO GROUP 'group8'";
    parseQueryThatShouldFail(sql8);
  }

  @Test
  public void testParseRevokeQuery() throws Exception {

    String sql0 = "REVOKE ON SCHEMA SYSTEM FROM 'user0'";
    parseQuery(sql0);
    String sql1 = "REVOKE ON SYSTEM.\"SEQUENCE\" FROM 'user1'";
    parseQuery(sql1);
    String sql2 = "REVOKE ON TABLE some_table2 FROM GROUP 'group2'";
    parseQuery(sql2);
    String sql3 = "REVOKE ON some_table3 FROM GROUP 'group2'";
    parseQuery(sql3);
    String sql4 = "REVOKE FROM 'user4'";
    parseQuery(sql4);
    String sql5 = "REVOKE FROM GROUP 'group5'";
    parseQuery(sql5);
    String sql6 = "REVOKE 'RRWWXAAA' FROM GROUP 'group6'";
    parseQueryThatShouldFail(sql6);
  }

  @Test
  public void testParsePreQuery0() throws Exception {
    String sql =
      (("select a from b\n" + "where ((ind.name = 'X')" + "and rownum <= (1000 + 1000))\n"));
    parseQuery(sql);
  }

  @Test
  public void testParsePreQuery1() throws Exception {
    String sql = (("select /*gatherSlowStats*/ count(1) from core.search_name_lookup ind\n"
      + "where( (ind.name = 'X'\n" + "and rownum <= 1 + 2)\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't'))"));
    parseQuery(sql);
  }

  @Test
  public void testParsePreQuery2() throws Exception {
    String sql = (("select /*gatherSlowStats*/ count(1) from core.custom_index_value ind\n"
      + "where (ind.string_value in ('a', 'b', 'c', 'd'))\n" + "and rownum <= ( 3 + 1 )\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.deleted = '0')\n" + "and (ind.index_num = 1)"));
    parseQuery(sql);
  }

  @Test
  public void testParsePreQuery3() throws Exception {
    String sql = (("select /*gatherSlowStats*/ count(1) from core.custom_index_value ind\n"
      + "where (ind.number_value > 3)\n" + "and rownum <= 1000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '001'\n"
      + "and (ind.deleted = '0'))\n" + "and (ind.index_num = 2)"));
    parseQuery(sql);
  }

  @Test
  public void testParsePreQuery4() throws Exception {
    String sql =
      (("select /*+ index(t iecustom_entity_data_created) */ /*gatherSlowStats*/ count(1) from core.custom_entity_data t\n"
        + "where (t.created_date > to_date('01/01/2001'))\n" + "and rownum <= 4500\n"
        + "and (t.organization_id = '000000000000000')\n" + "and (t.key_prefix = '001')"));
    parseQuery(sql);
  }

  @Test
  public void testCountDistinctQuery() throws Exception {
    String sql = (("select count(distinct foo) from core.custom_entity_data t\n"
      + "where (t.created_date > to_date('01/01/2001'))\n"
      + "and (t.organization_id = '000000000000000')\n" + "and (t.key_prefix = '001')\n"
      + "limit 4500"));
    parseQuery(sql);
  }

  @Test
  public void testIsNullQuery() throws Exception {
    String sql = (("select count(foo) from core.custom_entity_data t\n"
      + "where (t.created_date is null)\n" + "and (t.organization_id is not null)\n"));
    parseQuery(sql);
  }

  @Test
  public void testAsInColumnAlias() throws Exception {
    String sql = (("select count(foo) AS c from core.custom_entity_data t\n"
      + "where (t.created_date is null)\n" + "and (t.organization_id is not null)\n"));
    parseQuery(sql);
  }

  @Test
  public void testParseJoin1() throws Exception {
    String sql = (("select /*SOQL*/ \"Id\"\n" + "from (select /*+ ordered index(cft) */\n"
      + "cft.val188 \"Marketing_Offer_Code__c\",\n" + "t.account_id \"Id\"\n"
      + "from sales.account_cfdata cft,\n" + "sales.account t\n"
      + "where (cft.account_cfdata_id = t.account_id)\n"
      + "and (cft.organization_id = '00D300000000XHP')\n"
      + "and (t.organization_id = '00D300000000XHP')\n" + "and (t.deleted = '0')\n"
      + "and (t.account_id != '000000000000000'))\n"
      + "where (\"Marketing_Offer_Code__c\" = 'FSCR')"));
    parseQuery(sql);
  }

  @Test
  public void testParseJoin2() throws Exception {
    String sql = (("select /*rptacctlist 00O40000002C3of*/ \"00N40000001M8VK\",\n"
      + "\"00N40000001M8VK.ID\",\n" + "\"00N30000000r0K2\",\n" + "\"00N30000000jgjo\"\n"
      + "from (select /*+ ordered use_hash(aval368) index(cfa) */\n"
      + "a.record_type_id \"RECORDTYPE\",\n"
      + "aval368.last_name,aval368.first_name || ' ' || aval368.last_name,aval368.name \"00N40000001M8VK\",\n"
      + "a.last_update \"LAST_UPDATE\",\n" + "cfa.val368 \"00N40000001M8VK.ID\",\n"
      + "TO_DATE(cfa.val282) \"00N30000000r0K2\",\n" + "cfa.val252 \"00N30000000jgjo\"\n"
      + "from sales.account a,\n" + "sales.account_cfdata cfa,\n" + "core.name_denorm aval368\n"
      + "where (cfa.account_cfdata_id = a.account_id)\n" + "and (aval368.entity_id = cfa.val368)\n"
      + "and (a.deleted = '0')\n" + "and (a.organization_id = '00D300000000EaE')\n"
      + "and (a.account_id <> '000000000000000')\n"
      + "and (cfa.organization_id = '00D300000000EaE')\n"
      + "and (aval368.organization_id = '00D300000000EaE')\n"
      + "and (aval368.entity_id like '005%'))\n" + "where (\"RECORDTYPE\" = '0123000000002Gv')\n"
      + "AND (\"00N40000001M8VK\" is null or \"00N40000001M8VK\" in ('BRIAN IRWIN', 'BRIAN MILLER', 'COLLEEN HORNYAK', 'ERNIE ZAVORAL JR', 'JAMIE TRIMBUR', 'JOE ANTESBERGER', 'MICHAEL HYTLA', 'NATHAN DELSIGNORE', 'SANJAY GANDHI', 'TOM BASHIOUM'))\n"
      + "AND (\"LAST_UPDATE\" >= to_date('2009-08-01 07:00:00'))"));
    parseQuery(sql);
  }

  @Test
  public void testNegative1() throws Exception {
    String sql = (("select /*gatherSlowStats*/ count(1) core.search_name_lookup ind\n"
      + "where (ind.name = 'X')\n" + "and rownum <= 2000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't')"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.MISSING_TOKEN.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testNegative2() throws Exception {
    String sql = (("seelect /*gatherSlowStats*/ count(1) from core.search_name_lookup ind\n"
      + "where (ind.name = 'X')\n" + "and rownum <= 2000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't')"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 601 (42P00): Syntax error. Encountered \"seelect\" at line 1, column 1."));
    }
  }

  @Test
  public void testNegative3() throws Exception {
    String sql = (("select /*gatherSlowStats*/ count(1) from core.search_name_lookup ind\n"
      + "where (ind.name = 'X')\n" + "and rownum <= 2000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't'))"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage().contains(
        "ERROR 603 (42P00): Syntax error. Unexpected input. Expecting \"EOF\", got \")\" at line 6, column 26."));
    }
  }

  @Test
  public void testNegativeCountDistinct() throws Exception {
    String sql = (("select /*gatherSlowStats*/ max( distinct 1) from core.search_name_lookup ind\n"
      + "where (ind.name = 'X')\n" + "and rownum <= 2000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't')"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLFeatureNotSupportedException e) {
      // expected
    }
  }

  @Test
  public void testNegativeCountStar() throws Exception {
    String sql = (("select /*gatherSlowStats*/ max(*) from core.search_name_lookup ind\n"
      + "where (ind.name = 'X')\n" + "and rownum <= 2000\n"
      + "and (ind.organization_id = '000000000000000')\n" + "and (ind.key_prefix = '00T')\n"
      + "and (ind.name_type = 't')"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 601 (42P00): Syntax error. Encountered \"*\" at line 1, column 32."));
    }
  }

  @Test
  public void testNegativeNonBooleanWhere() throws Exception {
    String sql = (("select /*gatherSlowStats*/ max( distinct 1) from core.search_name_lookup ind\n"
      + "where 1"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLFeatureNotSupportedException e) {
      // expected
    }
  }

  @Test
  public void testCommentQuery() throws Exception {
    String sql =
      (("select a from b -- here we come\n" + "where ((ind.name = 'X') // to save the day\n"
        + "and rownum /* won't run */ <= (1000 + 1000))\n"));
    parseQuery(sql);
  }

  @Test
  public void testQuoteEscapeQuery() throws Exception {
    String sql = (("select a from b\n" + "where ind.name = 'X''Y'\n"));
    parseQuery(sql);
  }

  @Test
  public void testSubtractionInSelect() throws Exception {
    String sql = (("select a, 3-1-2, -4- -1-1 from b\n" + "where d = c - 1\n"));
    parseQuery(sql);
  }

  @Test
  public void testParsingStatementWithMispellToken() throws Exception {
    try {
      String sql = (("selects a from b\n" + "where e = d\n"));
      parseQuery(sql);
      fail("Should have caught exception.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 601 (42P00): Syntax error. Encountered \"selects\" at line 1, column 1."));
    }
    try {
      String sql = (("select a froms b\n" + "where e = d\n"));
      parseQuery(sql);
      fail("Should have caught exception.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 602 (42P00): Syntax error. Missing \"EOF\" at line 1, column 16."));
    }
  }

  @Test
  public void testParsingStatementWithExtraToken() throws Exception {
    try {
      String sql = (("select a,, from b\n" + "where e = d\n"));
      parseQuery(sql);
      fail("Should have caught exception.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 601 (42P00): Syntax error. Encountered \",\" at line 1, column 10."));
    }
    try {
      String sql = (("select a from from b\n" + "where e = d\n"));
      parseQuery(sql);
      fail("Should have caught exception.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage()
        .contains("ERROR 601 (42P00): Syntax error. Encountered \"from\" at line 1, column 15."));
    }
  }

  @Test
  public void testParseCreateTableInlinePrimaryKeyWithOrder() throws Exception {
    for (String order : new String[] { "asc", "desc" }) {
      String s = "create table core.entity_history_archive (id char(15) primary key ${o})"
        .replace("${o}", order);
      CreateTableStatement stmt = (CreateTableStatement) new SQLParser((s)).parseStatement();
      List<ColumnDef> columnDefs = stmt.getColumnDefs();
      assertEquals(1, columnDefs.size());
      assertEquals(SortOrder.fromDDLValue(order), columnDefs.iterator().next().getSortOrder());
    }
  }

  @Test
  public void testParseCreateTableOrderWithoutPrimaryKeyFails() throws Exception {
    for (String order : new String[] { "asc", "desc" }) {
      String stmt =
        "create table core.entity_history_archive (id varchar(20) ${o})".replace("${o}", order);
      try {
        new SQLParser((stmt)).parseStatement();
        fail("Expected parse exception to be thrown");
      } catch (SQLException e) {
        String errorMsg =
          "ERROR 603 (42P00): Syntax error. Unexpected input. Expecting \"RPAREN\", got \"${o}\""
            .replace("${o}", order);
        assertTrue(
          "Expected message to contain \"" + errorMsg + "\" but got \"" + e.getMessage() + "\"",
          e.getMessage().contains(errorMsg));
      }
    }
  }

  @Test
  public void testParseCreateTablePrimaryKeyConstraintWithOrder() throws Exception {
    for (String order : new String[] { "asc", "desc" }) {
      String s =
        "create table core.entity_history_archive (id CHAR(15), name VARCHAR(150), constraint pk primary key (id ${o}, name ${o}))"
          .replace("${o}", order);
      CreateTableStatement stmt = (CreateTableStatement) new SQLParser((s)).parseStatement();
      PrimaryKeyConstraint pkConstraint = stmt.getPrimaryKeyConstraint();
      List<Pair<ColumnName, SortOrder>> columns = pkConstraint.getColumnNames();
      assertEquals(2, columns.size());
      for (Pair<ColumnName, SortOrder> pair : columns) {
        assertEquals(SortOrder.fromDDLValue(order),
          pkConstraint.getColumnWithSortOrder(pair.getFirst()).getSecond());
      }
    }
  }

  @Test
  public void testParseCreateTableCommaBeforePrimaryKeyConstraint() throws Exception {
    for (String leadingComma : new String[] { ",", "" }) {
      String s =
        "create table core.entity_history_archive (id CHAR(15), name VARCHAR(150)${o} constraint pk primary key (id))"
          .replace("${o}", leadingComma);

      CreateTableStatement stmt = (CreateTableStatement) new SQLParser((s)).parseStatement();

      assertEquals(2, stmt.getColumnDefs().size());
      assertNotNull(stmt.getPrimaryKeyConstraint());
    }
  }

  private CreateCDCStatement parseCreateCDCSimple(String sql, boolean ifNotExists)
    throws Exception {
    CreateCDCStatement stmt = parseQuery(sql, CreateCDCStatement.class);
    assertEquals("FOO", stmt.getCdcObjName().getName());
    assertEquals("BAR", stmt.getDataTable().getTableName());
    assertEquals(ifNotExists, stmt.isIfNotExists());
    return stmt;
  }

  @Test
  public void testCreateCDCSimple() throws Exception {
    parseCreateCDCSimple("create cdc foo on bar", false);
    parseCreateCDCSimple("create cdc foo on s.bar", false);
    parseCreateCDCSimple("create cdc if not exists foo on bar", true);
    parseCreateCDCSimple("create cdc foo on bar", false);
    CreateCDCStatement stmt = null;
    stmt = parseCreateCDCSimple("create cdc foo on bar TTL=100", false);
    assertEquals(Arrays.asList(new Pair("TTL", 100)), stmt.getProps().get(""));
    stmt = parseCreateCDCSimple("create cdc foo on bar include (pre)", false);
    assertEquals(new HashSet<>(Arrays.asList(PTable.CDCChangeScope.PRE)), stmt.getIncludeScopes());
    stmt = parseCreateCDCSimple("create cdc foo on bar include (pre, post, change)", false);
    assertEquals(new HashSet<>(Arrays.asList(PTable.CDCChangeScope.PRE, PTable.CDCChangeScope.POST,
      PTable.CDCChangeScope.CHANGE)), stmt.getIncludeScopes());
    stmt = parseCreateCDCSimple("create cdc foo on bar include (pre, pre, post)", false);
    assertEquals(
      new HashSet<>(Arrays.asList(PTable.CDCChangeScope.PRE, PTable.CDCChangeScope.POST)),
      stmt.getIncludeScopes());
    stmt = parseCreateCDCSimple("create cdc if not exists foo on bar abc=def", true);
    assertEquals(Arrays.asList(new Pair("ABC", "def")), stmt.getProps().get(""));
    stmt = parseCreateCDCSimple("create cdc if not exists foo on bar abc=def, prop=val", true);
    assertEquals(Arrays.asList(new Pair("ABC", "def"), new Pair("PROP", "val")),
      stmt.getProps().get(""));
  }

  @Test
  public void testCreateCDCWithErrors() throws Exception {
    parseQueryThatShouldFail("create cdc foo");
    parseQueryThatShouldFail("create cdc foo on bar include (abc)");
  }

  private void parseInvalidCreateCDC(String sql, int expRrrorCode) throws IOException {
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(expRrrorCode, e.getErrorCode());
    }
  }

  @Test
  public void testInvalidCreateCDC() throws Exception {
    parseInvalidCreateCDC("create cdc foo bar", SQLExceptionCode.MISSING_TOKEN.getErrorCode());
    parseInvalidCreateCDC("create cdc foo bar ts", SQLExceptionCode.MISSING_TOKEN.getErrorCode());
    parseInvalidCreateCDC("create cdc foo bar(ts)", SQLExceptionCode.MISSING_TOKEN.getErrorCode());
    parseInvalidCreateCDC("create cdc s.foo on bar(ts)",
      SQLExceptionCode.MISMATCHED_TOKEN.getErrorCode());
    parseInvalidCreateCDC("create cdc foo bar(ts1, ts2)",
      SQLExceptionCode.MISSING_TOKEN.getErrorCode());
  }

  @Test
  public void testInvalidTrailingCommaOnCreateTable() throws Exception {
    String sql = (("create table foo (c1 varchar primary key, c2 varchar,)"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.MISMATCHED_TOKEN.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testCreateSequence() throws Exception {
    String sql = (("create sequence foo.bar\n" + "start with 0\n" + "increment by 1\n"));
    parseQuery(sql);
  }

  private DropCDCStatement parseDropCDCSimple(String sql, boolean ifNotExists) throws Exception {
    DropCDCStatement stmt = parseQuery(sql, DropCDCStatement.class);
    assertEquals("FOO", stmt.getCdcObjName().getName());
    assertEquals("BAR", stmt.getTableName().getTableName());
    assertEquals(ifNotExists, stmt.ifExists());
    return stmt;
  }

  @Test
  public void testDropCDCSimple() throws Exception {
    DropCDCStatement stmt = null;
    parseDropCDCSimple("drop cdc foo on bar", false);
    parseDropCDCSimple("drop cdc if exists foo on bar", true);
    parseDropCDCSimple("drop cdc if exists foo on s.bar", true);
  }

  private void parseInvalidDropCDC(String sql, int expRrrorCode) throws IOException {
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(expRrrorCode, e.getErrorCode());
    }
  }

  @Test
  public void testInvalidDropCDC() throws Exception {
    parseInvalidDropCDC("drop cdc foo bar", SQLExceptionCode.MISSING_TOKEN.getErrorCode());
    parseInvalidDropCDC("drop cdc s.foo on bar", SQLExceptionCode.MISMATCHED_TOKEN.getErrorCode());
    parseInvalidDropCDC("drop cdc foo on bar(ts)", SQLExceptionCode.MISSING_TOKEN.getErrorCode());
  }

  private void parseInvalidAlterCDC(String sql, int expRrrorCode) throws IOException {
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(expRrrorCode, e.getErrorCode());
    }
  }

  @Test
  public void testNextValueForSelect() throws Exception {
    String sql = (("select next value for foo.bar \n" + "from core.custom_entity_data\n"));
    parseQuery(sql);
  }

  @Test
  public void testNextValueForWhere() throws Exception {
    String sql = (("upsert into core.custom_entity_data\n"
      + "select next value for foo.bar from core.custom_entity_data\n"));
    parseQuery(sql);
  }

  @Test
  public void testBadCharDef() throws Exception {
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadVarcharDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col CHAR(0))");
      parseQuery(sql);
      fail("Should have caught bad char definition.");
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.NONPOSITIVE_MAX_LENGTH.getErrorCode(), e.getErrorCode());
    }
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadVarcharDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col CHAR)");
      parseQuery(sql);
      fail("Should have caught bad char definition.");
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.MISSING_MAX_LENGTH.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testBadVarcharDef() throws Exception {
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadVarcharDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col VARCHAR(0))");
      parseQuery(sql);
      fail("Should have caught bad varchar definition.");
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.NONPOSITIVE_MAX_LENGTH.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testBadDecimalDef() throws Exception {
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadDecimalDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col DECIMAL(0, 5))");
      parseQuery(sql);
      fail("Should have caught bad decimal definition.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage().contains(
        "ERROR 209 (22003): Decimal precision outside of range. Should be within 1 and 38. columnName=COL"));
    }
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadDecimalDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col DECIMAL(40, 5))");
      parseQuery(sql);
      fail("Should have caught bad decimal definition.");
    } catch (SQLException e) {
      assertTrue(e.getMessage(), e.getMessage().contains(
        "ERROR 209 (22003): Decimal precision outside of range. Should be within 1 and 38. columnName=COL"));
    }
  }

  @Test
  public void testBadBinaryDef() throws Exception {
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadBinaryDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col BINARY(0))");
      parseQuery(sql);
      fail("Should have caught bad binary definition.");
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.NONPOSITIVE_MAX_LENGTH.getErrorCode(), e.getErrorCode());
    }
    try {
      String sql = ("CREATE TABLE IF NOT EXISTS testBadVarcharDef"
        + "  (pk VARCHAR NOT NULL PRIMARY KEY, col BINARY)");
      parseQuery(sql);
      fail("Should have caught bad char definition.");
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.MISSING_MAX_LENGTH.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testPercentileQuery1() throws Exception {
    String sql =
      (("select PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary DESC) from core.custom_index_value ind"));
    parseQuery(sql);
  }

  @Test
  public void testPercentileQuery2() throws Exception {
    String sql =
      (("select PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY mark ASC) from core.custom_index_value ind"));
    parseQuery(sql);
  }

  @Test
  public void testRowValueConstructorQuery() throws Exception {
    String sql = (("select a_integer FROM aTable where (x_integer, y_integer) > (3, 4)"));
    parseQuery(sql);
  }

  @Test
  public void testSingleTopLevelNot() throws Exception {
    String sql = (("select * from t where not c = 5"));
    parseQuery(sql);
  }

  @Test
  public void testTopLevelNot() throws Exception {
    String sql = (("select * from t where not c"));
    parseQuery(sql);
  }

  @Test
  public void testRVCInList() throws Exception {
    String sql = (("select * from t where k in ( (1,2), (3,4) )"));
    parseQuery(sql);
  }

  @Test
  public void testInList() throws Exception {
    String sql = (("select * from t where k in ( 1,2 )"));
    parseQuery(sql);
  }

  @Test
  public void testInvalidSelectStar() throws Exception {
    String sql = (("select *,k from t where k in ( 1,2 )"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.MISSING_TOKEN.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testTableNameStartsWithUnderscore() throws Exception {
    String sql = (("select* from _t where k in ( 1,2 )"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.PARSER_ERROR.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testValidUpsertSelectHint() throws Exception {
    String sql = (("upsert /*+ NO_INDEX */ into t select k from t where k in ( 1,2 )"));
    parseQuery(sql);
  }

  @Test
  public void testPlainUpsertReturningRow() throws Exception {
    String sql = "upsert into t (k, v) values ( 1, 2 ) RETURNING *";
    UpsertStatement stmt = parseQuery(sql, UpsertStatement.class);
    assertTrue(stmt.isReturningRow());
  }

  @Test
  public void testPlainUpsertNotReturningRow() throws Exception {
    String sql = "upsert into t (k, v) values ( 1, 2 )";
    UpsertStatement stmt = parseQuery(sql, UpsertStatement.class);
    assertFalse(stmt.isReturningRow());
  }

  @Test
  public void testUpsertWithOnDuplicateKey() throws Exception {
    String sql = "upsert into t (k, v) values ( 1, 2 ) " + "ON DUPLICATE KEY UPDATE k = k + 1";
    parseQuery(sql);
  }

  @Test
  public void testUpsertInvalidReturningProjections() throws Exception {
    String sql =
      "upsert into t (k, v) values ( 1, 2 ) " + "ON DUPLICATE KEY UPDATE k = k + 1 RETURNING k";
    try {
      parseQuery(sql);
      fail();
    } catch (PhoenixParserException e) {
      assertEquals(SQLExceptionCode.MISMATCHED_TOKEN.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testUpsertReturningRow() throws Exception {
    String sql =
      "upsert into t (k, v) values ( 1, 2 ) " + "ON DUPLICATE KEY UPDATE k = k + 1 RETURNING *";
    UpsertStatement stmt = parseQuery(sql, UpsertStatement.class);
    assertTrue(stmt.isReturningRow());
  }

  @Test
  public void testDeleteReturningRow() throws Exception {
    String sql = "delete from t RETURNING *";
    parseQuery(sql);
  }

  @Test
  public void testDeleteWhereReturningRow() throws Exception {
    String sql = "DELETE FROM T WHERE PK1 = ? AND PK2 = ? RETURNING *";
    parseQuery(sql);
  }

  @Test
  public void testDeleteWithOrderLimitWhereReturningRow() throws Exception {
    String sql = "DELETE FROM T WHERE PK1 = ? AND PK2 = ? ORDER BY PK2 LIMIT 1 RETURNING *";
    parseQuery(sql);
  }

  @Test
  public void testDeleteInvalidReturningRow() throws Exception {
    String sql = "DELETE FROM T RETURNING PK1";
    try {
      parseQuery(sql);
      fail();
    } catch (PhoenixParserException e) {
      assertEquals(SQLExceptionCode.MISMATCHED_TOKEN.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testHavingWithNot() throws Exception {
    String sql = (("select\n" + "\"WEB_STAT_ALIAS\".\"DOMAIN\" as \"c0\"\n"
      + "from \"WEB_STAT\" \"WEB_STAT_ALIAS\"\n" + "group by \"WEB_STAT_ALIAS\".\"DOMAIN\" having\n"
      + "(\n" + "(\n" + "NOT\n" + "(\n" + "(sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null)\n"
      + ")\n" + "OR NOT((sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null))\n" + ")\n"
      + "OR NOT((sum(\"WEB_STAT_ALIAS\".\"ACTIVE_VISITOR\") is null))\n" + ")\n"
      + "order by CASE WHEN \"WEB_STAT_ALIAS\".\"DOMAIN\" IS NULL THEN 1 ELSE 0 END,\n"
      + "\"WEB_STAT_ALIAS\".\"DOMAIN\" ASC"));
    parseQuery(sql);
  }

  @Test
  public void testToDateInList() throws Exception {
    String sql = (("select * from date_test where d in (to_date('2013-11-04 09:12:00'))"));
    parseQuery(sql);
  }

  @Test
  public void testDateLiteral() throws Exception {
    String sql = (("select * from t where d = DATE '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testTimeLiteral() throws Exception {
    String sql = (("select * from t where d = TIME '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testTimestampLiteral() throws Exception {
    String sql = (("select * from t where d = TIMESTAMP '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testUnsignedDateLiteral() throws Exception {
    String sql = (("select * from t where d = UNSIGNED_DATE '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testUnsignedTimeLiteral() throws Exception {
    String sql = (("select * from t where d = UNSIGNED_TIME '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testUnsignedTimestampLiteral() throws Exception {
    String sql = (("select * from t where d = UNSIGNED_TIMESTAMP '2013-11-04 09:12:00'"));
    parseQuery(sql);
  }

  @Test
  public void testParseDateEquality() throws Exception {
    SQLParser parser = new SQLParser(
      new StringReader("select a from b\n" + "where date '2014-01-04' = date '2014-01-04'"));
    parser.parseStatement();
  }

  @Test
  public void testParseDateIn() throws Exception {
    SQLParser parser = new SQLParser(
      new StringReader("select a from b\n" + "where date '2014-01-04' in (date '2014-01-04')"));
    parser.parseStatement();
  }

  @Test
  public void testUnknownLiteral() throws Exception {
    String sql = (("select * from t where d = FOO '2013-11-04 09:12:00'"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.ILLEGAL_DATA.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testUnsupportedLiteral() throws Exception {
    String sql = (("select * from t where d = DECIMAL '2013-11-04 09:12:00'"));
    try {
      parseQuery(sql);
      fail();
    } catch (SQLException e) {
      assertEquals(SQLExceptionCode.TYPE_MISMATCH.getErrorCode(), e.getErrorCode());
    }
  }

  @Test
  public void testAnyElementExpression1() throws Exception {
    String sql = "select * from t where 'a' = ANY(a)";
    parseQuery(sql);
  }

  @Test
  public void testAnyElementExpression2() throws Exception {
    String sql = "select * from t where 'a' <= ANY(a-b+1)";
    parseQuery(sql);
  }

  @Test
  public void testAllElementExpression() throws Exception {
    String sql = "select * from t where 'a' <= ALL(a-b+1)";
    parseQuery(sql);
  }

  @Test
  public void testDoubleBackslash() throws Exception {
    String sql = "SELECT * FROM T WHERE A LIKE 'a\\(d'";
    parseQuery(sql);
  }

  @Test
  public void testUnicodeSpace() throws Exception {
    // U+2002 (8194) is a "EN Space" which looks just like a normal space (0x20 in ascii)
    String unicodeEnSpace = String.valueOf(Character.toChars(8194));
    String sql = Joiner.on(unicodeEnSpace).join(new String[] { "SELECT", "*", "FROM", "T" });
    parseQuery(sql);
  }

  @Test
  public void testInvalidTableOrSchemaName() throws Exception {
    // namespace separator (:) cannot be used
    parseQueryThatShouldFail("create table a:b (id varchar not null primary key)");
    parseQueryThatShouldFail("create table \"a:b\" (id varchar not null primary key)");
    // name separator (.) cannot be used without double quotes
    parseQueryThatShouldFail("create table a.b.c.d (id varchar not null primary key)");
    parseQuery("create table \"a.b\".\"c.d\" (id varchar not null primary key)");
    parseQuery("create table \"a.b.c.d\" (id varchar not null primary key)");
  }

  @Test
  public void testIntegerInOffsetSelect() throws Exception {
    String sql = "SELECT * FROM T OFFSET 1";
    parseQuery(sql);
  }

  @Test
  public void testRVCInOffsetSelect() throws Exception {
    String sql = "SELECT * FROM T OFFSET (A,B,C)=('a','b','c')";
    parseQuery(sql);
  }

  @Test
  public void testBindInOffsetSelect() throws Exception {
    String sql = "SELECT * FROM T OFFSET ?";
    parseQuery(sql);
  }

  @Test
  public void testLongQuery() throws Exception {
    String sql = "SELECT * FROM T WHERE a IN (1) OFFSET 1";
    parseQuery(sql);
  }

  @Test
  public void testLimitOffsetQuery() throws Exception {
    String sql = "SELECT * FROM T LIMIT 10 OFFSET 1";
    parseQuery(sql);
  }

  @Test
  public void testLimitRVCOffsetQuery() throws Exception {
    String sql = "SELECT * FROM T LIMIT 10 OFFSET (A,B,C)=('a','b','c')";
    parseQuery(sql);
  }

  @Test
  public void testShowStmt() throws Exception {
    // Happy paths
    parseQuery("show schemas");
    parseQuery("show schemas like 'foo%'");
    parseQuery("show tables");
    parseQuery("show tables in foo");
    parseQuery("show tables in foo like 'bar%'");
    parseQuery("show tables like 'bar%'");

    // Expected failures.
    parseQueryThatShouldFail("show schemas like foo");
    parseQueryThatShouldFail("show schemas in foo");
    parseQueryThatShouldFail("show tables 'foo'");
    parseQueryThatShouldFail("show tables in 'foo'");
    parseQueryThatShouldFail("show tables like foo");
  }

  @Test
  public void testCreateSchema() throws Exception {
    String sql0 = "create schema \"schema1\"";
    parseQuery(sql0);
    String sql1 = "create schema schema1";
    parseQuery(sql1);
    String sql2 = "create schema \"default\"";
    parseQuery(sql2);
    String sql3 = "create schema \"DEFAULT\"";
    parseQuery(sql3);
  }

  @Test
  public void testShowCreateTable() throws Exception {
    // Happy paths
    parseQuery("SHOW CREATE TABLE FOO");
    parseQuery("show create table FOO");
    parseQuery("SHOW CREATE TABLE s.FOO");
    parseQuery("SHOW CREATE TABLE \"foo\"");
    parseQuery("SHOW CREATE TABLE s.\"foo\"");
    parseQuery("SHOW CREATE TABLE \"s\".FOO");

    // Expected failures.
    parseQueryThatShouldFail("SHOW CREATE VIEW foo");
    parseQueryThatShouldFail("SHOW CREATE TABLE 'foo'");
  }

  @Test
  public void testBinaryLiteral() throws Exception {
    // As per ISO/IEC 9075-2:2011(E) 5.3 <literal> page 163
    // The literal syntax is:
    //
    // <binary string literal> ::=
    // X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
    // [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
    // <hexit> [ <space>... ] }... ] <quote> }... ]
    //
    // With the current grammar we only approximate the multi-line syntax, but
    // we're close enough for most practical purposes
    // (We do not enforce a new line before continuation lines)

    // Happy paths
    parseQuery("SELECT b, x from x WHERE x = x'00'");
    parseQuery(
      "SELECT b, x from x WHERE x = " + "x'0 12 ' --comment \n /* comment */ '34 567' \n \n 'aA'");
    parseQuery("SELECT b, x from x WHERE x = "
      + "b'0 10 ' --comment \n /* comment */ '10 101' \n \n '00000000'");

    // Expected failures.
    // Space after 'x'
    parseQueryThatShouldFailWithSQLException("SELECT b, x from x WHERE x = x '00'");
    parseQueryThatShouldFailWithSQLException("SELECT b, x from x WHERE b = b '00'");

    // Illegal digit character in first line
    parseQueryThatShouldFail("SELECT b, x from x WHERE x = "
      + "x'X0 12 ' --comment \n /* comment */ '34 5670' \n \n 'aA'");
    parseQueryThatShouldFail("SELECT b, x from b WHERE b = "
      + "b'B0 10 ' --comment \n /* comment */ '10 101' \n \n '000000000'");

    // Illegal digit character in continuation line
    parseQueryThatShouldFail("SELECT b, x from x WHERE x = "
      + "x'0 12 ' --comment \n /* comment */ '34 5670' \n \n 'aA_'");
    parseQueryThatShouldFail("SELECT b, x from x WHERE x = "
      + "b'0 10 ' --comment \n /* comment */ '00 0000' \n \n '00_'");

    // No digit between quotes in continuation line
    parseQueryThatShouldFail(
      "SELECT b, x from x WHERE x = " + "x'0 12 ' --comment \n /* comment */ '34 5670' \n \n ''");
    parseQueryThatShouldFail(
      "SELECT b, x from x WHERE x = " + "b'0 10 ' --comment \n /* comment */ '00 000' \n \n ''");
  }
}
